<?php

function get_latest_trainer_member() {
  $query = db_select('users', 'u')->extend('PagerDefault');
  $query->leftJoin('field_data_field_customer_name', 'fcn', 'u.uid=fcn.revision_id');
  $query->leftJoin('field_data_field_customer_surname', 'fcs', 'u.uid=fcs.revision_id');
  $query->fields('u', array('uid', 'mail', 'name'));
  $query->addField('fcn', 'field_customer_name_value', 'fcn_name');
  $query->addField('fcs', 'field_customer_surname_value', 'surname');
  $query->condition('u.status', 1);
  $query->orderBy('u.uid', 'DESC');
  $query->limit(12);
  return $query->execute();
}

function get_trainer_testimonial() {
  $query = db_select('node', 'n')->extend('PagerDefault');
  $query->innerJoin('field_data_field_description', 'fd', 'n.vid=fd.revision_id');
  $query->innerJoin('field_data_field_image_testimonial', 'fit', 'n.vid=fit.revision_id');
  $query->innerJoin('file_managed', 'fm', 'fit.field_image_testimonial_fid=fm.fid');
  $query->innerJoin('field_data_field_trainer_customer', 'ftc', 'n.vid=ftc.revision_id');
  $query->innerJoin('url_alias', 'ua', "ua.source = CONCAT('node/', n.nid)");
  $query->fields('n', array('vid', 'type', 'title'));
  $query->addField('fd', 'field_description_value', 'description');
  $query->addField('fm', 'uri', 'uri_image');
  $query->addField('ua', 'alias', 'href');
  $query->condition('ftc.field_trainer_customer_value', 'trainer', '=');
  $query->condition('n.type', 'testimonials');
  $query->condition('n.status', 1);
  $query->limit(2);
  return $query->execute();
}

function get_trainer_online() {
  $query = db_select('users', 'u')->extend('PagerDefault');
  $query->innerJoin('sessions', 's', 'u.uid=s.uid');
  $query->innerJoin('users_roles', 'ur', 'u.uid=ur.uid');
  $query->innerJoin('role', 'r', 'ur.rid=r.rid');
  $query->innerJoin('field_data_field_customer_name', 'fcn', 'u.uid=fcn.revision_id');
  $query->innerJoin('field_data_field_customer_surname', 'fcs', 'u.uid=fcs.revision_id');
  $query->fields('u', array('uid', 'mail', 'name'));
  $query->addField('fcn', 'field_customer_name_value', 'fcn_name');
  $query->addField('fcs', 'field_customer_surname_value', 'surname');
  $query->condition('r.name', 'trainer');
  $query->groupBy('s.uid');
  $query->limit(4);
  return $query->execute();
}

function get_video_pre_recorded_video() {
  $query = db_select('node', 'n');
  $query->leftJoin('field_data_field_gender', 'fg', 'n.uid=fg.revision_id');
  $query->innerJoin('field_data_field_video_pre_video_recorded', 'pvr', 'n.nid=pvr.revision_id');
  $query->innerJoin('field_data_field_video_cost', 'fvc', 'n.nid=fvc.revision_id');
  $query->innerJoin('file_managed', 'fm', 'pvr.field_video_pre_video_recorded_fid=fm.fid');
  $query->innerJoin('url_alias', 'ua', "ua.source = CONCAT('node/', n.nid)");
  $query->fields('n', array('nid', 'type', 'title', 'uid'));
  $query->addField('fvc', 'field_video_cost_value', 'cost');
  $query->addField('fm', 'uri');
  $query->addField('ua', 'alias');
  $query->addField('fg', 'field_gender_value' , 'gender');
  $query->condition('n.status', 1);
  $query->condition('n.type', 'video');
  $query->condition(db_or()->condition('fm.filemime', 'image/png', '=')->condition('fm.filemime', 'image/jpeg', '=')->condition('fm.filemime', 'image/jpg', '='));
  return $query->execute();
}

function tvn_get_qna()
{
    $query = db_select('node', 'n');
    $query->innerJoin('field_data_field_question', 'q', 'n.vid = q.revision_id');
    $query->innerJoin('field_data_body', 'b', 'n.vid = b.revision_id');
    $query->fields('q', array('field_question_value'));
    $query->fields('b', array('body_value'));
    $query->fields('n', array('nid','title'));
    $query->condition('n.type', 'faq');
    $query->orderBy('n.created', 'DESC');
    return $query->execute();
}